const express = require('express');
const router = express.Router();

// 获取排行榜
router.get('/:type', async (req, res) => {
  try {
    const { type } = req.params;
    const { page = 1, limit = 20, difficulty } = req.query;
    const offset = (page - 1) * limit;
    
    // 验证排行榜类型
    const validTypes = ['score', 'time', 'level'];
    if (!validTypes.includes(type)) {
      return res.status(400).json({ error: '无效的排行榜类型' });
    }
    
    const db = req.app.locals.db;
    
    let query, orderBy, params = [];
    
    switch (type) {
      case 'score':
        query = `
          SELECT 
            id,
            difficulty,
            score,
            time_spent,
            created_at
          FROM games 
          WHERE completed = 1
        `;
        orderBy = 'ORDER BY score DESC, time_spent ASC';
        break;
        
      case 'time':
        query = `
          SELECT 
            id,
            difficulty,
            score,
            time_spent,
            created_at
          FROM games 
          WHERE completed = 1
        `;
        orderBy = 'ORDER BY time_spent ASC, score DESC';
        break;
        
      case 'level':
        query = `
          SELECT 
            difficulty,
            COUNT(*) as games_played,
            AVG(score) as avg_score,
            AVG(time_spent) as avg_time,
            MAX(score) as best_score,
            MIN(time_spent) as best_time,
            COUNT(CASE WHEN completed = 1 THEN 1 END) as completed_games
          FROM games 
          GROUP BY difficulty
        `;
        orderBy = 'ORDER BY avg_score DESC';
        break;
    }
    
    // 添加难度筛选
    if (difficulty && type !== 'level') {
      query += ' AND difficulty = ?';
      params.push(difficulty);
    }
    
    // 添加排序和分页
    if (type !== 'level') {
      query += ` ${orderBy} LIMIT ? OFFSET ?`;
      params.push(parseInt(limit), parseInt(offset));
    } else {
      query += ` ${orderBy}`;
    }
    
    console.log('SQL查询:', query);
    console.log('参数数组:', params);
    
    // 使用标准的参数化查询
    const [results] = await db.execute(query, params);
    
    // 获取总数（除了level类型）
    let total = results.length;
    if (type !== 'level') {
      let countQuery = 'SELECT COUNT(*) as total FROM games WHERE completed = 1';
      let countParams = [];
      
      if (difficulty) {
        countQuery += ' AND difficulty = ?';
        countParams.push(difficulty);
      }
      
      const [countResult] = await db.execute(countQuery, countParams);
      total = countResult[0].total;
    }
    
    res.json({
      success: true,
      data: {
        leaderboard: results,
        type,
        pagination: type !== 'level' ? {
          page: parseInt(page),
          limit: parseInt(limit),
          total,
          pages: Math.ceil(total / limit)
        } : null
      }
    });
  } catch (error) {
    console.error('获取排行榜失败:', error);
    res.status(500).json({ error: '获取排行榜失败' });
  }
});

// 获取个人排名
router.get('/my-rank/:gameId', async (req, res) => {
  try {
    const { gameId } = req.params;
    const { type = 'score' } = req.query;
    
    const db = req.app.locals.db;
    
    // 获取指定游戏的信息
    const [gameInfo] = await db.execute(
      'SELECT * FROM games WHERE id = ? AND completed = 1',
      [gameId]
    );
    
    if (gameInfo.length === 0) {
      return res.status(404).json({ error: '游戏记录不存在或未完成' });
    }
    
    const game = gameInfo[0];
    
    let rankQuery;
    switch (type) {
      case 'score':
        rankQuery = `
          SELECT COUNT(*) + 1 as rank
          FROM games 
          WHERE completed = 1 
            AND difficulty = ?
            AND (score > ? OR (score = ? AND time_spent < ?))
        `;
        break;
        
      case 'time':
        rankQuery = `
          SELECT COUNT(*) + 1 as rank
          FROM games 
          WHERE completed = 1 
            AND difficulty = ?
            AND (time_spent < ? OR (time_spent = ? AND score > ?))
        `;
        break;
        
      default:
        return res.status(400).json({ error: '无效的排名类型' });
    }
    
    const [rankResult] = await db.execute(rankQuery, [
      game.difficulty,
      type === 'score' ? game.score : game.time_spent,
      type === 'score' ? game.score : game.time_spent,
      type === 'score' ? game.time_spent : game.score
    ]);
    
    // 获取该难度下的总游戏数
    const [totalResult] = await db.execute(
      'SELECT COUNT(*) as total FROM games WHERE completed = 1 AND difficulty = ?',
      [game.difficulty]
    );
    
    res.json({
      success: true,
      data: {
        rank: rankResult[0].rank,
        total: totalResult[0].total,
        game: {
          id: game.id,
          difficulty: game.difficulty,
          score: game.score,
          time_spent: game.time_spent,
          created_at: game.created_at
        },
        percentage: Math.round((1 - (rankResult[0].rank - 1) / totalResult[0].total) * 100)
      }
    });
  } catch (error) {
    console.error('获取个人排名失败:', error);
    res.status(500).json({ error: '获取个人排名失败' });
  }
});

// 获取今日排行榜
router.get('/today/:type', async (req, res) => {
  try {
    const { type } = req.params;
    const { limit = 10, difficulty } = req.query;
    
    const validTypes = ['score', 'time'];
    if (!validTypes.includes(type)) {
      return res.status(400).json({ error: '无效的排行榜类型' });
    }
    
    const db = req.app.locals.db;
    
    let query = `
      SELECT 
        id,
        difficulty,
        score,
        time_spent,
        created_at
      FROM games 
      WHERE completed = 1 
        AND DATE(created_at) = CURDATE()
    `;
    
    let params = [];
    
    if (difficulty) {
      query += ' AND difficulty = ?';
      params.push(difficulty);
    }
    
    query += ` ORDER BY ${type === 'score' ? 'score DESC, time_spent ASC' : 'time_spent ASC, score DESC'} LIMIT ?`;
    params.push(parseInt(limit));
    
    const [results] = await db.execute(query, params);
    
    res.json({
      success: true,
      data: {
        leaderboard: results,
        type,
        date: new Date().toISOString().split('T')[0]
      }
    });
  } catch (error) {
    console.error('获取今日排行榜失败:', error);
    res.status(500).json({ error: '获取今日排行榜失败' });
  }
});

module.exports = router;